﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using BaseData;
using BaseData.Common;
using TaxEntity.SysConfig;

namespace TaxData.SysConfig
{
    /// <summary>
    /// 街道数据层
    /// </summary>
    public class JiedaoData
    {
        /// <summary>
        /// 获取街道信息实体
        /// </summary>
        public JiedaoEntity GetEntityById(int ID)
        {
            string sql = "select * from Jiedao with(nolock) where ID = @ID and status=1";
            SqlParameter p = new SqlParameter("@ID", SqlDbType.Int, 8);
            p.Value = ID;
            DataTable dt = DataConfig.QueryRecords(BusinessType.DBRead, sql, p);
            JiedaoEntity model = this.GetEntityHelper(dt);
            return model;
        }

        /// <summary>
        /// 删除街道
        /// </summary>
        public bool Delete(int ID)
        {
            string sql = "update Jiedao set status=0 where ID = @ID";
            SqlParameter p = new SqlParameter("@ID", SqlDbType.Int, 8);
            p.Value = ID;
            int affectRows = DataConfig.QueryNoRecords(BusinessType.DBWrite, sql, p);
            return affectRows > 0;
        }

        /// <summary>
        /// 获取街道列表
        /// </summary>
        public DataTable GetList(string strWhere, int pageSize, int page, ref int allCount)
        {
            DataTable dt = new DataTable();
            string condition = " where status=1" + strWhere;
            string sqlCount = "select count(1) from Jiedao with(nolock) " + condition;
            allCount = (int)DataConfig.GetSingle(BusinessType.DBRead, sqlCount);
            if (allCount > 0)
            {
                int startNum = (page - 1) * pageSize + 1;
                int endNum = page * pageSize;
                string sqlInner = "select *,ROW_NUMBER() over(order by Code) as RowNumber,xiangzhenName=(select top 1 Name from Xiangzhen where ID = XiangzhenID) from Jiedao with(nolock) " + condition;
                string sqlUserList = string.Format("SELECT * FROM ({0}) T WHERE T.RowNumber BETWEEN {1} AND {2} order by RowNumber asc", sqlInner, startNum, endNum);
                dt = DataConfig.QueryRecords(BusinessType.DBRead, sqlUserList);
            }
            return dt;
        }

        /// <summary>
        /// 获取街道列表
        /// </summary>
        public DataTable GetList(int xingzhenID)
        {
            string sql = "select * from Jiedao with(nolock) where status=1 and XiangzhenID=" + xingzhenID + " order by Code";
            DataTable dt = DataConfig.QueryRecords(BusinessType.DBRead, sql);
            return dt;
        }

        /// <summary>
        /// 增加街道
        /// </summary>
        /// <param name="model">街道信息实体</param>
        /// <returns>成功街道编号,失败返回0</returns>
        public int Add(JiedaoEntity model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();
            if (model.Code != null)
            {
                strSql1.Append("Code,");
                strSql2.Append("'" + model.Code + "',");
            }
            if (model.Name != null)
            {
                strSql1.Append("Name,");
                strSql2.Append("'" + model.Name + "',");
            }
            if (model.XiangzhenID != null)
            {
                strSql1.Append("XiangzhenID,");
                strSql2.Append("" + model.XiangzhenID + ",");
            }
            if (model.OptUserID != null)
            {
                strSql1.Append("OptUserID,");
                strSql2.Append("" + model.OptUserID + ",");
            }
            if (model.Status != null)
            {
                strSql1.Append("Status,");
                strSql2.Append("" + model.Status + ",");
            }
            strSql.Append("insert into Jiedao(");
            strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
            strSql.Append(")");
            strSql.Append(";select @@IDENTITY");
            object obj = DataConfig.GetSingle(BusinessType.DBWrite, strSql.ToString());
            int ID = 0;
            if (obj != null && obj != DBNull.Value)
            {
                int.TryParse(obj.ToString(), out ID);
            }
            return ID;
        }

        /// <summary>
        /// 修改街道信息
        /// </summary>
        /// <param name="model">修改街道</param>
        /// <returns>操作结果</returns>
        public bool Update(JiedaoEntity model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Jiedao set ");
            if (model.Code != null)
            {
                strSql.Append("Code='" + model.Code + "',");
            }
            if (model.Name != null)
            {
                strSql.Append("Name='" + model.Name + "',");
            }
            if (model.XiangzhenID != null)
            {
                strSql.Append("XiangzhenID=" + model.XiangzhenID + ",");
            }
            if (model.OptUserID != null)
            {
                strSql.Append("OptUserID=" + model.OptUserID + ",");
            }
            if (model.Status != null)
            {
                strSql.Append("Status=" + model.Status + ",");
            }
            int n = strSql.ToString().LastIndexOf(",");
            strSql.Remove(n, 1);
            strSql.Append(" where ID=" + model.ID + "");
            int rows = DataConfig.QueryNoRecords(BusinessType.DBWrite, strSql.ToString());
            return rows > 0;
        }

        /// <summary>
        /// 从DataTable中得到一个实体对象
        /// </summary>
        private JiedaoEntity GetEntityHelper(DataTable dt)
        {
            JiedaoEntity model = null;
            if (dt != null && dt.Rows.Count > 0)
            {
                model = new JiedaoEntity();

                if (dt.Rows[0]["ID"] != null && dt.Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(dt.Rows[0]["ID"].ToString());
                }
                if (dt.Rows[0]["Code"] != null && dt.Rows[0]["Code"].ToString() != "")
                {
                    model.Code = dt.Rows[0]["Code"].ToString();
                }
                if (dt.Rows[0]["Name"] != null && dt.Rows[0]["Name"].ToString() != "")
                {
                    model.Name = dt.Rows[0]["Name"].ToString();
                }
                if (dt.Rows[0]["XiangzhenID"] != null && dt.Rows[0]["XiangzhenID"].ToString() != "")
                {
                    model.XiangzhenID = int.Parse(dt.Rows[0]["XiangzhenID"].ToString());
                }
                if (dt.Rows[0]["OptUserID"] != null && dt.Rows[0]["OptUserID"].ToString() != "")
                {
                    model.OptUserID = long.Parse(dt.Rows[0]["OptUserID"].ToString());
                }
                if (dt.Rows[0]["Status"] != null && dt.Rows[0]["Status"].ToString() != "")
                {
                    model.Status = int.Parse(dt.Rows[0]["Status"].ToString());
                }
            }
            return model;
        }
    }
}